USE [CTEM-DB-v1]
GO

/****** Object:  StoredProcedure [dbo].[Employee_GetEmployee]    Script Date: 02/10/2012 11:24:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		TienDH
-- Create date: 12/05/2011
-- Description:	Get full employee's information
-- =============================================
CREATE PROCEDURE [dbo].[Employee_GetEmployee]
	@username	NVARCHAR(50),
	@fullname	NVARCHAR(50),	
	@roleName	NVARCHAR(50),
	@status		INT
AS
BEGIN
	IF @status = -1
	BEGIN
		SELECT	E.EmployeeID, E.Username,E.Password,E.FullName,
				E.Address,E.Email,E.Note,
				E.RoleID, E.RightsValue,E.CMND,
				E.DOB,E.PhoneNumber, E.IsActive, E.TotalDebt, R.RoleName
		FROM	Employee E JOIN Role R ON E.RoleID = R.RoleID	
		WHERE	R.RoleName LIKE N'%' + @roleName + '%' AND
				R.RoleName NOT LIKE N'%admin%' AND
				E.Username LIKE N'%' + @username + '%' AND
				E.FullName LIKE N'%' + @fullname + '%'
	END
	
	IF @status <> -1
	BEGIN
		SELECT	E.EmployeeID, E.Username,E.Password,E.FullName,
				E.Address,E.Email,E.Note,
				E.RoleID, E.RightsValue,E.CMND,
				E.DOB,E.PhoneNumber, E.IsActive, E.TotalDebt, R.RoleName
		FROM	Employee E JOIN Role R ON E.RoleID = R.RoleID	
		WHERE	R.RoleName LIKE N'%' + @roleName + '%' AND
				R.RoleName NOT LIKE N'%admin%' AND
				E.Username LIKE N'%' + @username + '%' AND
				E.FullName LIKE N'%' + @fullname + '%' AND
				E.IsActive = CONVERT(BIT, @status)
	END
END

GO

